# Copyright (c) 2024, json_wang and contributors
# For license information, please see license.txt

import frappe


def execute(filters=None):
    filters = frappe._dict(filters or {})
    columns = [
        {'fieldname':'项目编号','label':'项目编号','fieldtype':'Data','align':'right','width':200},
        {'fieldname':'项目名称','label':'项目名称','fieldtype':'Data','align':'right','width':200},
        {'fieldname':'核算日期','label':'核算日期','fieldtype':'Date','align':'right','width':100},
        {'fieldname':'用户ID','label':'用户ID','fieldtype':'Data','align':'right','width':150},
        {'fieldname':'姓名','label':'姓名','fieldtype':'Data','align':'right','width':100},
        {'fieldname':'角色','label':'角色','fieldtype':'Data','align':'right','width':100},        
        {'fieldname':'核算金额','label':'核算金额','fieldtype':'Currency','align':'right','width':100}        
        ]
    query_sql = """
select a.name 项目编号,a.project_name 项目名称,a.issue_date as 核算日期,b.`user` 用户ID,b.user_fullname 姓名,b.`role` 角色,b.paid 核算金额  from 
tabCP_Issue_Bonus a
inner join
tabCP_Issue_Bonus_Item  b on a.name = b.parent
where 1=1"""
    roles = frappe.get_roles()
    if "System Manager" not in roles and "财务会计" not in roles and "部门主管" not in roles:
        query_sql = query_sql + " and b.`user` = '" + frappe.session.user + "'"
    if filters.start_date:
        query_sql = query_sql + " and a.issue_date >= '" + filters.start_date + "'"
    if filters.end_date:
        query_sql = query_sql + " and a.issue_date <= '" + filters.end_date + "'"        
    # print(query_sql)
    data = frappe.db.sql(query_sql,as_dict=True)
    return columns, data
